package bean;
import java.sql.*;
import bean.DBConnection;
import bean.User;
public class UserDAO {
	public static int insert(User user) throws SQLException {
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		conn = DBConnection.getConnection();
		int row = 0;
		String sql = "insert into [user] values(?,?,?)";
 
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, user.getUsername());
			pstm.setString(2, user.getPassword());
			pstm.setInt(3, user.getTag());
			row = pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBConnection.closeDB(conn, pstm, rs);
		}
		return row;
	}
	
	public static int delete(User user) throws SQLException {
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		conn = DBConnection.getConnection();
		int row = 0;
		String sql = "delete from [user] where id=?";
 
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, user.getId());
			row = pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBConnection.closeDB(conn, pstm, rs);
		}
		return row;
	}	
	
	public static int update(User user) throws SQLException {
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		conn = DBConnection.getConnection();
		int row = 0;
		String sql = "update [user] set password=? where username=?";
 
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, user.getPassword());
			pstm.setString(2, user.getUsername());
			row = pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBConnection.closeDB(conn, pstm, rs);
		}
		return row;
	}
	
	public static User retrieve(User user) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		conn = DBConnection.getConnection();
		User user2 = null;
		String sql = "select * from [user] where username=? and password=?";
		
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getUsername());
			pstmt.setString(2, user.getPassword());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				user2 = new User();
				user2.setId(rs.getInt("id"));
				user2.setUsername(rs.getString("username"));
				user2.setPassword(rs.getString("password"));
				user2.setTag(rs.getInt("tag"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBConnection.closeDB(conn, pstmt, rs);
		}
		return user2;
	}
}